
const pool = require('@/client/mysqlClient');
async function add(table, values) {
  const columns = Object.keys(values).join(',');
  const placeholders = Object.keys(values).map(() => '?').join(',');
  const sql = `INSERT INTO ${table} (${columns}) VALUES (${placeholders})`;
  try {
    const [result] = await pool.execute(sql, Object.values(values));
    return result;
  } catch (error) {
    throw error;
  }
}


async function remove(table, condition) {
  const sql = `DELETE FROM ${table} WHERE ${Object.keys(condition)[0]} = ?`;
  try {
    const [result] = await pool.execute(sql, Object.values(condition));
    return result;
  } catch (error) {
    throw error;
  }
}

async function update(table, values, condition) {
  const setClause = Object.entries(values).map(([key, value]) => `${key} = ?`).join(',');
  const sql = `UPDATE ${table} SET ${setClause} WHERE ${Object.keys(condition)[0]} = ?`;
  console.log("sqls",sql)
  try {
    const [result] = await pool.execute(sql, [...Object.values(values), ...Object.values(condition)]);
    return result;
  } catch (error) {
    throw error;
  }
}

async function getOne(table, condition) {
  const sql = `SELECT * FROM ${table} WHERE ${Object.keys(condition)[0]} = ?`;
  
  try {
    const [rows] = await pool.execute(sql, Object.values(condition));
    return rows[0];
  } catch (error) {
    throw error;
  }
}

async function getAll(table, conditions = {}, orderBy = null, sortOrder = 'ASC') {
  const whereClause = Object.keys(conditions).length > 0 ? `WHERE ${Object.keys(conditions).map(key => `${key} = ?`).join(' AND ')}` : '';
  const orderClause = orderBy ? `ORDER BY ${orderBy} ${sortOrder}` : '';
  const sql = `SELECT * FROM ${table} ${whereClause} ${orderClause}`;
  try {
    const [rows] = await pool.execute(sql, Object.values(conditions));
    return rows;
  } catch (error) {
    throw error;
  }
}


async function getAllByLike(table, conditions = {}, orderBy = null, sortOrder = 'ASC') {
  const whereClauses = [];
  const params = [];

  for (const key in conditions) {
    if (typeof conditions[key] === 'string' && conditions[key].includes('%')) {
      whereClauses.push(`${key} LIKE ?`);
      params.push(conditions[key]);
    } else {
      whereClauses.push(`${key} = ?`);
      params.push(conditions[key]);
    }
  }

  const whereClause = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
  const orderClause = orderBy ? `ORDER BY ${orderBy} ${sortOrder}` : '';
  const sql = `SELECT * FROM ${table} ${whereClause} ${orderClause}`;
  console.log("sql",sql)
  try {
    const [rows] = await pool.execute(sql, params);
    return rows;
  } catch (error) {
    throw error;
  }
}

async function getAllByLimit(table, conditions = {}, orderBy = null, sortOrder = 'ASC', page, pageSize) {
  const whereClauses = [];
  const params = [];

  for (const key in conditions) {
    if (typeof conditions[key] === 'string' && conditions[key].includes('%')) {
      whereClauses.push(`${key} LIKE ?`);
      params.push(conditions[key]);
    } else {
      whereClauses.push(`${key} = ?`);
      params.push(conditions[key]);
    }
  }

  const whereClause = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
  const orderClause = orderBy ? `ORDER BY ${orderBy} ${sortOrder}` : '';
  const offset = (page - 1) * pageSize; // 计算偏移量
  const limitClause = page && pageSize ?`LIMIT ${offset}, ${pageSize} `: ''; // 分页参数

  const sql = `SELECT * FROM ${table} ${whereClause} ${orderClause} ${limitClause}`;
  console.log("sql",sql)
  try {
    const [rows] = await pool.execute(sql, params);
    return rows;
  } catch (error) {
    throw error;
  }
}



async function getTotal(table, conditions = {}) {
  const whereClause = Object.keys(conditions).length > 0 ? `WHERE ${Object.keys(conditions).map(key => `${key} = ?`).join(' AND ')}` : '';
  const sql = `SELECT COUNT(*) AS total FROM ${table} ${whereClause}`;
  try {
    const [rows] = await pool.execute(sql, Object.values(conditions));
    return rows[0].total;
  } catch (error) {
    throw error;
  }
}



async function addMuti(table, values) {
  const columns = Object.keys(values[0]).join(',');
  const placeholders = Object.keys(values[0]).map(() => '?').join(',');

  // 构建 SQL 语句
  const sql = `INSERT INTO ${table} (${columns}) VALUES ${values.map(() => `(${placeholders})`).join(',')}`;
  const payload = values.flatMap(obj => Object.values(obj));
  try {
    // 执行 SQL 语句
    const [result] = await pool.execute(sql, payload);
    return result;
  } catch (error) {
    console.error('Error executing SQL:', error);
    throw error;
  }
}


async function deleteRoleMuti(table, roleId, fields, ids) {
  let sql = `DELETE FROM ${table} WHERE role_id=${roleId} AND ${fields} IN (${ids});`
  console.log(sql)
  try {
    // 执行 SQL 语句
    const [result] = await pool.execute(sql);
    return result;
  } catch (error) {
    console.error('Error executing SQL:', error);
    throw error;
  }
}


module.exports = {
  add,
  remove,
  update,
  getOne,
  getAll,
  getAllByLike,
  getAllByLimit,
  getTotal,
  addMuti,
  deleteRoleMuti
};